package com.wgworkshop.pems.repository;

import com.wgworkshop.pems.model.ClassArrangeByClassRecord;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 * 课程安排库自定义接口实现类。
 * Created by wlc on 2017/12/26.
 */
public class ClassArrangeRepositoryImpl implements ClassArrangeRepositoryCustom {
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Iterable<ClassArrangeByClassRecord> getArrangeByClass() {
        String sql = "SELECT DATE_FORMAT(ca.start_date, '%w') AS week, ca.class_num AS class_num, COUNT(ca.student_id) AS student_count " +
                "FROM class_arrange AS ca " +
                "WHERE ca.status = 1 " +
                "GROUP BY week, class_num " +
                "ORDER BY week ASC, class_num ASC ";
        Query query = entityManager.createNativeQuery(sql, "ClassArrangeByClassRecordMapping");
        return query.getResultList();
    }
}
